﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using QLPM.DTO;

namespace QLPM.DAO
{
    public class KhamBenhDAO
    {
        DataProvider provider;

        public KhamBenhDAO()
        {
            provider = new DataProvider();
        }

        public int ThemVaoDSKhamBenh(KhamBenh kb)
        {
            String sql = String.Format("insert into KHAMBENH(MaBenhNhan,NgayKham,MaLoaiBenh) values({0},'{1}',1)",kb.MaBenhNhan, kb.NgayKham);
            return provider.Excute_Non_Query(sql);
        }

        public int LayMaKhamBenh(KhamBenh kb)
        {
            String sql = String.Format("select * from KHAMBENH where MaBenhNhan = {0} and NgayKham = '{1}'", kb.MaBenhNhan, kb.NgayKham);
            provider = new DataProvider("KHAMBENH", sql);
            DataTable dt = provider;
            int makb = Convert.ToInt32(dt.Rows[0]["MaKhamBenh"].ToString());
            return makb;
        }

        public int CapNhatTongTien(int tongtien, int makhambenh)
        {
            String sql = String.Format("update KHAMBENH set TongTien = {0} where MaKhamBenh = {1}", tongtien, makhambenh);
            return provider.Excute_Non_Query(sql);
        }

        public int KiemTraBN(int mabn, String ngaykham)
        {
            String sql = String.Format(" select count(*)as ts from KHAMBENH where MaBenhNhan={0} and NgayKham = '{1}'", mabn, ngaykham);
            provider = new DataProvider("KHAMBENH", sql);
            DataTable dt = provider;
            int kq = Convert.ToInt32(dt.Rows[0]["ts"].ToString());
            return kq;
        }

        public int ThemTrieuChung(string trieuChung, int maLoaiBenh, int maKhamBenh)
        {
            string sql = String.Format("update KHAMBENH set TrieuChung='{0}',MaLoaiBenh={1} where MaKhamBenh={2}", trieuChung, maLoaiBenh, maKhamBenh);
            return provider.Excute_Non_Query(sql);
        }

        public DataTable DoanhThuTheoNgay(int Thang, int Nam)
        {
            string sql = String.Format("SELECT KHAMBENH.NgayKham, Count(KHAMBENH.MaBenhNhan) AS SoBenhNhan, Sum(KHAMBENH.TongTien) AS DoanhThu, Sum(TongTien)/(SELECT SUM(TongTien) FROM KHAMBENH WHERE MONTH(NgayKham)={0} and YEAR(NgayKham)={1}) AS TyLe FROM KHAMBENH WHERE (((Month([NgayKham]))={0}) AND ((Year([NgayKham]))={1}))GROUP BY KHAMBENH.NgayKham ", Thang, Nam);
            provider = new DataProvider("KHAMBENH", sql);
            DataTable dt = provider;
            return dt;
        }
    }
}
